using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using AccountingApp.Models;

namespace AccountingApp.Test
{
    public class TestDatabaseHelper
    {
        // 使用硬编码的连接字符串避免配置文件问题
        private static string connectionString = "Data Source=test_accounting.db;Cache=Shared;";

        public static void InitializeDatabase()
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // 创建用户表
                string createUsersTable = @"
                    CREATE TABLE IF NOT EXISTS Users (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        Username TEXT UNIQUE NOT NULL,
                        Password TEXT NOT NULL,
                        CreateTime DATETIME NOT NULL
                    )";

                // 创建记账记录表
                string createRecordsTable = @"
                    CREATE TABLE IF NOT EXISTS AccountRecords (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        UserId INTEGER NOT NULL,
                        Amount DECIMAL(10,2) NOT NULL,
                        Type TEXT NOT NULL,
                        Category TEXT NOT NULL,
                        Description TEXT,
                        RecordTime DATETIME NOT NULL,
                        FOREIGN KEY (UserId) REFERENCES Users(Id)
                    )";

                using (var command = new SQLiteCommand(createUsersTable, connection))
                {
                    command.ExecuteNonQuery();
                }

                using (var command = new SQLiteCommand(createRecordsTable, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        public static bool RegisterUser(User user)
        {
            try
            {
                using (var connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    string sql = "INSERT INTO Users (Username, Password, CreateTime) VALUES (@username, @password, @createTime)";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@username", user.Username);
                        command.Parameters.AddWithValue("@password", user.Password);
                        command.Parameters.AddWithValue("@createTime", user.CreateTime);
                        command.ExecuteNonQuery();
                        return true;
                    }
                }
            }
            catch
            {
                return false;
            }
        }

        public static User LoginUser(string username, string password)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT Id, Username, Password, CreateTime FROM Users WHERE Username = @username AND Password = @password";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@username", username);
                    command.Parameters.AddWithValue("@password", password);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return new User
                            {
                                Id = reader.GetInt32(0),
                                Username = reader.GetString(1),
                                Password = reader.GetString(2),
                                CreateTime = reader.GetDateTime(3)
                            };
                        }
                    }
                }
            }
            return null;
        }

        public static bool AddAccountRecord(AccountRecord record)
        {
            try
            {
                using (var connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    string sql = @"INSERT INTO AccountRecords (UserId, Amount, Type, Category, Description, RecordTime) 
                                   VALUES (@userId, @amount, @type, @category, @description, @recordTime)";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@userId", record.UserId);
                        command.Parameters.AddWithValue("@amount", record.Amount);
                        command.Parameters.AddWithValue("@type", record.Type);
                        command.Parameters.AddWithValue("@category", record.Category);
                        command.Parameters.AddWithValue("@description", record.Description ?? "");
                        command.Parameters.AddWithValue("@recordTime", record.RecordTime);
                        command.ExecuteNonQuery();
                        return true;
                    }
                }
            }
            catch
            {
                return false;
            }
        }

        public static List<AccountRecord> GetAccountRecords(int userId)
        {
            var records = new List<AccountRecord>();
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = @"SELECT Id, UserId, Amount, Type, Category, Description, RecordTime 
                               FROM AccountRecords WHERE UserId = @userId ORDER BY RecordTime DESC";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@userId", userId);
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            records.Add(new AccountRecord
                            {
                                Id = reader.GetInt32(0),
                                UserId = reader.GetInt32(1),
                                Amount = reader.GetDecimal(2),
                                Type = reader.GetString(3),
                                Category = reader.GetString(4),
                                Description = reader.GetString(5),
                                RecordTime = reader.GetDateTime(6)
                            });
                        }
                    }
                }
            }
            return records;
        }

        public static decimal GetBalance(int userId)
        {
            decimal balance = 0;
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = @"SELECT SUM(CASE WHEN Type = '收入' THEN Amount ELSE -Amount END) as Balance 
                               FROM AccountRecords WHERE UserId = @userId";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@userId", userId);
                    var result = command.ExecuteScalar();
                    if (result != DBNull.Value && result != null)
                    {
                        balance = Convert.ToDecimal(result);
                    }
                }
            }
            return balance;
        }
    }
}
